<?php

namespace app\common\model;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\facade\Request;

class Export
{
    /**
     * 对Spreadsheet方法封装.
     *
     * @param array  $data     [['sheetname'=>'sheet名字','title'=>['id'=>'id','name'=>'名字'],'data'=>[数组]]]该数组如果为一维数组，则填写一行，如果为二维数组，则多行数据
     * @param string $filename 下载Excel的文件名，可忽略
     * @param string $fileType 选择文件类型，如果不填写，默认下载为xlsx格式，如果任意填写数值为xls格式
     */
    public static function multiSheetExport(array $data, $filename = '', $fileType = 'Xlsx')
    {
        //文件名处置
        if (empty($filename)) {
            $filename = date('YmdHis') . '_' . rand(1000, 9999);
        } else {
            $filename = $filename . '_' . date('YmdHis');
        }
        $filename = urlencode($filename);
        //内容设置
        $preadsheet = new Spreadsheet();
        $sheet = $preadsheet->getActiveSheet();
        $word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V'];
        foreach ($data as $sheetK => $arr) {
            if (!$sheet) {
                $sheet = new Worksheet($preadsheet);
                $preadsheet->addSheet($sheet, $sheetK);
            }
            $sheet->getDefaultColumnDimension()->setWidth(12);
            if (!empty($arr['sheetname'])) {
                $sheet->setTitle($arr['sheetname']);
            }
            $i = 0;
            foreach ($arr['title'] as $k => $v) {
                $sheet->setCellValue($word[$i] . '1', $v);
                ++$i;
            }
            foreach ($arr['data'] as $k => $v) {
                $i = 0;
                foreach ($arr['title'] as $k2 => $v2) {
                    $sheet->setCellValue($word[$i] . ($k + 2), $v[$k2]);
                    ++$i;
                }
            }
            if (isset($data[$sheetK + 1])) {
                $sheet = [];
            }
        }
        unset($data);

        //样式设置
        //设置下载与后缀
        if ('Xlsx' == $fileType) {
            header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $suffix = 'xlsx';
        } else {
            header('Content-Type:application/vnd.ms-excel');
            $fileType = 'Xls';
            $suffix = 'xls';
        }
        header('Access-Control-Expose-Headers: Content-Disposition');
        header("Content-Disposition:attachment;filename=$filename.$suffix");
        header('Cache-Control:max-age=0'); //缓存控制
        $writer = IOFactory::createWriter($preadsheet, $fileType);
        $writer->save('php://output'); //数据流
    }

    /*
     单个sheet 导出
     $titleArr .eg. ['id'=>'id','name'=>'名字']
     $data .eg. [['id'=>'id','name'=>'名字'],['id'=>'id','name'=>'名字']]
    */
    public static function staticExport(array $data, array $titleArr = [], $filename = '', $fileType = 'Xlsx', $sheetName = '')
    {
        //文件名处置
        if (empty($filename)) {
            $filename = date('YmdHis') . '_' . rand(1000, 9999);
        } else {
            $filename = $filename . '_' . date('YmdHis');
        }
        $filename = urlencode($filename);
        //内容设置
        $preadsheet = new Spreadsheet();
        $sheet = $preadsheet->getActiveSheet();
        if ($sheetName) {
            $sheet->setTitle($sheetName);
        }
        $word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V'];

        $i = 0;
        $dataRow = 1;
        if (!$titleArr) {
            $titleArr = array_keys($data[0]);
            $titleArr = array_combine($titleArr, $titleArr);
        }

        if ($titleArr) {
            foreach ($titleArr as $k => $v) {
                $sheet->setCellValue($word[$i] . '1', $v);
                ++$i;
            }
            ++$dataRow;
        }

        foreach ($data as $k => $v) {
            $i = 0;
            foreach ($titleArr as $k2 => $v2) {
                $sheet->setCellValue($word[$i] . ($k + $dataRow), $v[$k2]);
                ++$i;
            }
        }
        unset($data);
        //样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);
        //设置下载与后缀
        if ('Xlsx' == $fileType) {
            header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $suffix = 'xlsx';
        } else {
            header('Content-Type:application/vnd.ms-excel');
            $fileType = 'Xls';
            $suffix = 'xls';
        }
        header('Access-Control-Expose-Headers: Content-Disposition');
        header("Content-Disposition:attachment;filename=$filename.$suffix");
        header('Cache-Control:max-age=0'); //缓存控制
        $writer = IOFactory::createWriter($preadsheet, $fileType);
        $writer->save('php://output'); //数据流
    }

    /**
     * 对Spreadsheet方法封装.
     *
     * @param array  $arr  该数组必须为键值对，键是表格单元，值是单元格的值
     * @param array  $data 该数组如果为一维数组，则填写一行，如果为二维数组，则多行数据
     * @param string $name 下载Excel的文件名，可忽略
     * @param string $type 选择文件类型，如果不填写，默认下载为xlsx格式，如果任意填写数值为xls格式
     */
    public function downloadExcel(array $arr, array $data, $name = '', $type = 'Xlsx')
    {
        //文件名处置
        if (empty($name)) {
            $name = date('YmdHis') . '_' . rand(1000, 9999);
        } else {
            $name = $name . '_' . date('YmdHis');
        }
        $name = urlencode($name);
        //内容设置
        $preadsheet = new Spreadsheet();
        $sheet = $preadsheet->getActiveSheet();
        $word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V'];
        foreach ($arr as $k => $v) {
            // $key = $word[$k] . '1';
            if ($k > 25) {
                $key = chr(65 + ($k / 26) - 1) . chr(65 + ($k % 26)) . '1';
            } else {
                $key = chr(65 + $k) . '1';
            }

            $sheet->setCellValue($key, $v);
        }

        $sheet->fromArray($data, null, 'A2', true);

        //样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);
        //设置下载与后缀
        if ('Xlsx' == $type) {
            header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $suffix = 'xlsx';
        } else {
            header('Content-Type:application/vnd.ms-excel');
            $type = 'Xls';
            $suffix = 'xls';
        }
        header('Access-Control-Expose-Headers: Content-Disposition');
        header("Content-Disposition:attachment;filename=$name.$suffix");
        header('Cache-Control:max-age=0'); //缓存控制
        $writer = IOFactory::createWriter($preadsheet, $type);
        $writer->save('php://output'); //数据流
    }

    /**
     *  导出订单excel
     *  type 1话费订单 2资金明细. 3、权益订单.
     */
    public function doExport($data, $type)
    {
        $name = '表格';
        $exportData = $title = [];
        switch ($type) {
            case  1:
                $fieldAttr = [
                    'supplier_order_no', 'sub_order_id', 'out_order_id', 'supplier_name', 'agent_name', 'company_name',
                    'sale_price',
                    'mobile', 'settle_price', 'amount', 'isp_type', 'order_type', 'supplier_status', 'created_at', 'finish_time', 'remark'
                ];
                $exportData = $this->getData($data, $fieldAttr);
                $title = [
                    '供应商订单号', '系统订单号', 'API接口订单号', '供应商', '代理商用户名', '代理商公司名称',
                    '销售价', '结算价', '成本价', '代理商利润', '平台利润', '充值号码', '充值金额', '运营商', '充值类型',
                    '充值状态', '下单时间', '完成时间', '备注'
                ];
                $name = '话费订单';
                break;
            case 2:
                $fieldAttr = ['type_name', 'agent_name', 'old_balance', 'amount', 'new_balance', 'remark', 'created_at'];
                $exportData = $this->getData($data, $fieldAttr);
                $title = ['类型', '代理商', '变动前金额', '变动金额', '变动后金额', '备注', '创建时间'];
                break;
            case 3:
                $fieldAttr = [
                    'out_order_id', 'order_id', 'goods_name', 'quantity', 'amount', 'pay_amount', 'platform_profit', 'profit', 'account', 'status_text', 'mobile', 'created_at',
                    'pay_type_text', 'pay_time', 'complete_at', 'agent_name', 'supplier_name',
                ];
                $exportData = $this->getData($data, $fieldAttr);
                $title = [
                    '接口订单号', '系统订单号', '商品名称', '数量', '订单总额', '实付金额', '平台利润', '代理商利润', '充值号码', '订单状态', '手机号', '下单时间', '支付方式', '支付时间',
                    '完成时间', '代理商', '供应商',
                ];
                $name = '权益订单';
                break;
        }
        //excel数据字段名
        $res = $this->downloadExcel($title, $exportData, $name);

        return $res;
    }

    /**
     * 处理获得data.
     */
    public function getData($data, $fieldAttr)
    {
        $export = [];
        $ispType = ['', '移动', '联通', '电信'];
        $supplierStatus = ['正在充值', '正在充值', '正在充值', '充值成功', '充值失败'];
        foreach ($data as $nk => $nv) {
            $onceData = [];
            foreach ($fieldAttr as $k => $v) {
                switch ($v) {
                    case 'isp_type':
                        $onceData[] = isset($ispType[$nv[$v]]) ? $ispType[$nv[$v]] : '未知';
                        break;
                    case 'order_type':
                        $onceData[] = 1 == $nv[$v] ? '慢充' : '快充';
                        break;
                    case 'supplier_status':
                        $onceData[] = isset($supplierStatus[$nv[$v]]) ? $supplierStatus[$nv[$v]] : '正在充值';
                        break;
                    case 'old_balance':
                    case 'new_balance':
                    case 'amount':
                    case 'pay_amount':
                    case 'settle_price':
                        $onceData[] = fen2yuan($nv[$v]);
                        break;
                    case 'out_order_id':
                    case 'order_id':
                    case 'sub_order_id':
                        $onceData[] = ' ' . $nv[$v] . ' ';
                        break;
                    default:
                        $onceData[] = $nv[$v];
                        break;
                }
            }
            $export[] = $onceData;
        }

        return $export;
    }

    /**
     * 导入excel.
     */
    public function importExcel()
    {
        $file = request()->file('excel');
        $data = [];
        if ($file) {
            try {
                $file = request()->file('excel');
                if (empty($file)) {
                    return fail('请选择需要导入的Excel表格');
                }
                $file_types = explode('.', $_FILES['excel']['name']);
                $file_type = $file_types[count($file_types) - 1]; //xls后缀
                if ('xls' != strtolower($file_type) && 'xlsx' != strtolower($file_type)) {
                    return fail('格式错误，请上传正确的excel文件！');
                }
                $maxSize = 4194304;
                if ($_FILES["excel"]["size"] > $maxSize) {
                    return fail('文件大小不能超过4M！');
                }
                $filename = uniqid() . $_FILES['excel']['name'];
                // 上传到本地服务器
                $savename = \think\facade\Filesystem::disk('public')->putFileAs('excel', $file, $filename);
                $savename = app()->getRootPath() . 'public/storage/' . str_replace('\\', '/', $savename);
                $data = $this->import_exl($savename);
                unlink($savename);
            } catch (\Exception $e) {
                return fail('导入失败：' . $e->getMessage());
            }

            return  $data;
        }
    }

    public function import_exl($uploadfile)
    {
        $inputFileType = IOFactory::identify($uploadfile); //传入Excel路径
        $excelReader = IOFactory::createReader($inputFileType); //Xlsx
        $PHPExcel = $excelReader->load($uploadfile); // 载入excel文件
        $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
        $sheetdata = $sheet->toArray();

        return $sheetdata; // --- 直接返回数组数据
    }
}
